1. Can anyone say Stored procedures pros and cons AND Java Stored Procedures pros and con? stackoverflow.comCan anyone say Stored procedures pros and cons AND Java Stored Procedures pros and con? And how to test it. Best regards! |
2. What is the advantage of using stored procedures to do insert/update/delete/select? stackoverflow.comI am currently looking at some code that is doing even trivial insert/select/update by running stored procedures. So the code basically does |
3. Stored Procedure failure on JDBC test run jmeter.512774.n5.nabble.comHello, I am attempting to run an existing stored procedure on Jmeter passing a variable to it. I keep getting diff exception errors on the result tree using these 3 popular commands. 1- BEGIN GET_USER_BY_EMAIL_SP(); end; Throws SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'GET_USER_BY_EMAIL_SP' must be declared (On this one, I placed the variable in the ... |
4. CloudScape Stored Procedures coderanch.comThere are some pointers available now in the Cloudscape FAQ at http://www.cloudscape.com/support/faq.jsp. Look under the SQL topic. Cloudscape uses Java as its stored procedure language, and provides Class and Method aliases as a way to make invoking them in SQL use the same SQL as other vendors. You simply define a static public method in a class, and then you can ... |
5. JDBC, Stored Procedure and Rowtype coderanch.comDear Madhira. I've just crawled through a page that answers your (and also my) problem with Procedures that returns//accept rowTypes. I attach you the part that worths: (taken from Akadia) Stored Function with Record Set The PL/SQL package java_demo defines the procedure list_emp(), using the IN parameter p_job and the result parameter as ResultSet, analogous to the attributes of the table ... |
6. Java Stored Procedure coderanch.comIt really depends on what your stored procedure is doing. Based on the name, it looks like your class is attempting some sort of DML - dropping a table would be my guess. If memory serves me correctly - it's been a while since I've developed with Oracle - Oracle Java stored procedures limit what you can do with them. Primarily, ... |
7. Java stored procedures coderanch.com |
8. problem with stored procedure....... coderanch.comHi all! I tested my stored procedure in SQL query analyser & I get the expected return value.. but while retrieving it at the front end with callable statement in a string I always get empty string . If I try to retrieve it in integer variable then also as I don't get any returnvalue I get the Null pointer exception. ... |
9. stored procedures in a loop... what gives? coderanch.comWithout using stored procedures I get the desired results. When I use the first stored procedure the results are fine. When I add an additional stored procedure within the loop it bombs. Giving me the following message (stackTrace): java.sql.SQLException: [Microsoft][ODBC SQL Server Driver] Connection is busy with results for another hstmt at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031) at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188) at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:2533) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:217) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:92) ... |
10. Can I get the Stored procedure's code from JDBC coderanch.com |
11. JDBC and stored procedure - Please Help!!! coderanch.com |
12. Can you invoke a Java class from a PL/SQL stored procedure? coderanch.com |
13. Regarding reultset in stored procedures coderanch.comOriginally posted by Jamie Robertson: String procedure = "{? = CALL someStoredProcedure(?, ?,...)}"; CallableStatement cstmt = con.prepareCall(procedure); ResultSet rs = cstmt.execute(); 2. as an output parameter String procedure = "{CALL someStoredProcedure(?, ?,...)}"; CallableStatement cstmt = con.prepareCall(procedure); cstmt.registerOutParameter(1, Types.OTHER); //this is resultset output parameter ... cstmt.execute(); //method to get resultset as out parameter depends on database This depends. I have training as ... |
14. HELP! Need to learn stored procedures ASAP coderanch.com |
15. Stored procedure & auto commit coderanch.comIf I have my SQL code inside a stored proc and I want the store proc to execute within a transaction; does setting the autocommit to false achieve this or do I need to do a BEGIN TRANSACTION/END TRANSACTION within the stored proc. Another related question is that I've set the isolation level to "Serializable" in the connection object. Is this ... |
16. Problem with Stored Procedure coderanch.comCurrently I have a stored procedure which evaluates three scenarios and appends to a temporary table depending on the selection. if 1 insert temp table if 2 insert temp table if 3 insert temp table display temp table On the command line this works fine, but when I call this stored procedure using the jdbc-odbc bridge I can only retrieve a ... |
17. Stored Procedure coderanch.com |
18. What am I doing wrong in this Stored Procedure coderanch.comI have written a stored procedure for MSSQL 2000 procedure search_result(@name VARCHAR,@address varchar) AS IF((@name IS NOT NULL AND @name <> '' AND @name <> ' ') AND(@address IS NULL OR @address <> '' OR @address <> ' ')) BEGIN SELECT [postal_code] FROM OM WHERE [name] LIKE '%@name%' END ..... GO Nothing is being returned I have tried it in the ... |
19. JDBC vs PL/SQL Stored Procedures coderanch.comEric, Performance problems can be caused by vairous means. Before you get too carried away in optimizing individual SQL queries, you may want to review the following checklist: 1. How many SQL queries are you running off your page? 2. Are you using connection pooling to avoid unneccessary DataSource instantiation? 3. Run your SQL query in a console tool (Enterprise manager ... |
20. SybSQLException: Stored procedure 'xxx be run only in un chained mode coderanch.comcom.sybase.jdbc2.jdbc.SybSQLException: Stored procedure 'ap_SystemAccess' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode. I am trying to execute procedure, //cs.executeUpdate("SET CHAINED OFF"); // String sptest = " { call sp_procxmode ccsys.dbo.ap_SystemAccess" + "," + " anymode }" ; //CallableStatement aStmt = con.prepareCall(sptest); //boolean rc12 =aStmt.execute(); // rs ... |
21. How can I make this stored procedure work coderanch.comIm using MSSQL 2000 im trying to write a stored procedure which first checks if a certain day is already stored if not store the day and the owner and return something to show if it was stored or not create procedure dbo.pickDay @day datetime, @owner varchar(255), @number tinyint AS IF EXISTS (SELECT day FROM calendar WHERE [date] = @day) INSERT ... |
22. Stored Procedures coderanch.com |
23. Stored procedures in DB2/WAS coderanch.com |
24. Nested Stored Procedures - Java - Urgent!!!!!!!! coderanch.com |
25. How to Execute Stored Procedure? coderanch.com |
26. Java Stored Procedure coderanch.comI am executing a stored procedure with no luck. Here's the senario... private void foo(String value) { Connection conn = null; PreparedStatement stmt = null; String decryptedValue = null; conn = DBconnection.getConnection("CONNECTION"); CallableStatement cstmt = conn.prepareCall( "{? = call DECRYPT(?)}"); cstmt.registerOutParameter(1, java.sql.Types.LONGVARCHAR); cstmt.setString(2, value); cstmt.execute(); String obj = cstmt.getString(1); decryptedValue = obj.toString(); System.out.println("Value of variable is " + decryptedValue); } This ... |
27. design strategies, database views and stored procedures coderanch.comYears ago folks sitting down to design an app would design the database first and then design the program that would use that data. Now, we design the program first and when we have determined the application's needs for object persistance, we design the database tables. For the last ten years, when I have worked with a database and left to ... |
28. Using JDBC versus Stored Procedures coderanch.comHello - I am currently in a dilema, and don't have much time to benchmark different approaches. Here is my problem: I am reading in a flat file (approximately 100,000 records) into my Java application, processing this file (performing specific business logic) and then need to update my Oracle database with these records. Unfortunately, I have many foreign keys set up ... |
29. creat a Stored Procedures in pgsql? coderanch.com |
30. Getting Result Set from a Stored Procedure coderanch.com |
31. stored procedure using jdbc-odbc bridge coderanch.com |
32. Question about stored procedure coderanch.com |
33. Create stored procedure in hssql coderanch.comIf what you say is true, and hssql supports stored procedures, they should support the SQL standard for creating them, which is something like: create procedure procedure_name as select statement goes here And that's it. You can get a lot fancier by adding variables to the select statement for the stored procedure to deal with so as to not make everything ... |
34. examples for stored procedures coderanch.com |
35. stored procedure coderanch.comI have some insert statements,I would like to convert these insert statement into stored procedure and make the query most efficient how to go about it? If anybody worked on stored procedures and converting simple statements to sotred procedures please reply with converted stored procedure and how to make the below statement most efficient by optimizing the joins.? Please go through ... |
36. Stored procedures in MSAcess coderanch.com'Fraid not Sri Gnana. Triggers are not supported in MSAccess. Neither are SPs (unless its 2002). But then again MSAccess is certainly not robust, secure, or feature rich enough for any professional application to actually use it as a DB. You should probably go back to your client and advise that they are about to do something very stupid. This seems ... |
37. Stored Procedure question coderanch.comHi Dan, As Avi says, it's not very clear what you're trying to do as this is a lot of ambiguity. If I understand you correctly you have two stored procedures that contain the two queries you've supplied (or do you just have two queries?). Regardless, you want to combine them into one query (or do you just want to use ... |
38. Stored Procedure Problem coderanch.com |
39. Prepared Stmt or Stored Procedure ? coderanch.comI'm going to disagree, but you'll find as many opinions as people 'round these parts Personally I never use Stored Procedures unless I need some extremely database specific behaviour, maybe like reporting. The problem with them is that they force you to start pushing your domain logic into the database, and if you're writing a multi-tiered application you could end up ... |
40. Java Stored Procedure coderanch.com |
41. How can I get the list of only user defined Stored Procedures? coderanch.com |
42. StoredProcedure coderanch.com |
43. stored procedure coderanch.com |
44. How to Read DBMS_OUTPUT from a stored procedure in a java program coderanch.comSrini, I use OUT parameters when I need to return more than one value from a (PL/SQL) routine -- since a function can only return one value. If I only return one value, then I prefer to use a function. I don't know if there's a performance difference between using OUT parameters or functions. Good Luck, Avi. |
45. Stored procedures in JDBC? coderanch.com |
46. A Tricky Stored Procedure coderanch.comThis may be getting a bit off topic, but it's still primarily SQL so, hopefully, I won't get too bad of a thrashing for posting this here. I'm trying to write a rather complicated stored procedure (in MS SQL Server 2000). It accesses a lot of tables and uses a number of temporary tables to store intermediate data. Let me try ... |
47. Stored Procedures coderanch.comStored Procedures are stored in the DB schema they were created in. And normally they will be written in DB-specific procedural language - PL/SQL in the case of Oracle. Postgres does not support PL/SQL, it does however support PL/Perl, PL/Python, PL/pgSQL and PL/TCL. None are exactly like PL/SQL, but will look pretty simmilar. You will have to change all your SPs ... |
48. java stored procedure problems coderanch.comHey all, at this moment Im so confused about creating/using java stored procedures in an Oracle database (9i) that I may not even know how to ask a logical questionso please bear with me. Im coding a sample JSP to try to understand how java stored procedures work, Im using a basic MVC architecture to do this. I start with an ... |
49. StoredProcedure coderanch.com |
50. Problem with Stored procedure coderanch.com |
51. Stored procedures.. coderanch.com |
52. question reg. a stored procedure coderanch.comI am trying to write a stored procedure , where I want the data for the last 6 days from a table irrespective of whether the data is present or not. and I want to count the no. of times an ID appears for each of the dates. Example : ID 7/27/2005 7/28/2005 7/29/2005 7/30/2005 7/31/2005 8/1/2005 AA 1 5 4 ... |
53. When to use stored procedure coderanch.comElizabeth, Stored procs are good when you are doing a lot of database manipulation. For example, get records from a bunch of tables with different table structures or unusual relationships and merge them into one record. While this could be done in JDBC, it would involve a tremendous amount of network traffic. Another case would be if performance testing shows a ... |
54. Stored procedures in J2EE coderanch.comSome J2EE apps use big databases. One of the problems as I understand with SPs is one of scalability: they may work well with a small DB, but what happens when you need to filter on a DB that has 50 or more tables? In this case, I would be quite surprised if SPs with optional parameters could perform as well ... |
55. Stored Procedures coderanch.com |
56. Search Stored Procedure coderanch.com |
57. Stored Procedure in Hypersonic coderanch.comHi derby, I m using Hypersonic DB with Java. I have created database. Now i want to create Stored Procedure. Is Hypersonic supports User defined Stored Procedure ? Because when i use command like "create procedure ....." then it gives error "unexpected token PROCEDURE". So please tell me how to create Stored Procedure in Hypersonic DB. If it does not support ... |
58. Stored Functions Vs Stored Procedures coderanch.com |
59. stored procedure inside another stored procedure coderanch.com |
60. Difference between Stored Procedures and Functions coderanch.comTotally depends on you, wanting sth returned or not. Say in one case, I had to ignore any exception returned, and in case of any just would inform the end user by getting "-1" as return from a function. Actually from the point of the database they (proc. and fnctn) are no different. |
61. Parsing a String value in StoredProcedure??? coderanch.com |
62. Stored procedures using JWorks coderanch.comNeed Help, using Stored procedures using JWorks. I am having problems returning values from the stored procedure (single return values, I Could get result sets though). public Map runStoredProcedure( String callString, Map parameters, String a) throws Exception { ItcDatabase db = null; ItcSqlMgr sqlMgr = null; ItcDataObject dobjRecord; Map recordSetMap = new HashMap(); System.out.println("New Function"); try { db = getConnection(); sqlMgr ... |
63. Stored Procedure problem coderanch.comHi, I am just trying to run a stored procedure in SQL Server 2000. Its a vary basic SP except that I need the table name to be a variable. I tried this; -- ============================================= -- Create procedure basic template -- ============================================= -- creating the store procedure IF EXISTS (SELECT name FROM sysobjects WHERE name = N'testing' AND type = 'P') ... |
64. Should I use or avoid Stored procedures? coderanch.comStored procs ususally are faster, but be sure to perform relevant timings before just assuming that they will in your case. Cross-platform compatibiliy is another concern. If you need to support different dabatases then an approach involvin stored procs is harder to maintain than one that only uses standard SQL. Stored procs have (IMO) the drawback that business logic has a ... |
65. stored procedure coderanch.com |
66. Stored procedure not being refreshed while accessing through code coderanch.comHello all, I m using Tomacat to run a web-application. Used stored procedure to access data from MSSQL Server. But when i re-run the application, it always calls the old stored procedure,while the result displayed in the Query Analyser is the modified one. Tried restarting the server as well as deleting the work folder and the generated class files too. But ... |
67. disadvantage of stored procedures coderanch.comOriginally posted by naidu gulla: hi javaranchers, i would like to know the disadvantages of storedprocedures. thanks in advance Different databases have different stored procedure languages; some databases don't support stored procedures at all. While at least one database supports Java as a stored procedure language, most don't, so Java programmers who are going to write stored procedures must learn an ... |
68. testing a stored procedure coderanch.comI spent some time trying to define variable in Toad but without any success. Looks like Toad doesn't support SQL* Plus features like define/print/variable. One way you can test the procedure is to execute the procedure in the anonymous block by defining local variables for the return values and using dbms_output.put_line. This is more work than using SQL* Plus. The other ... |
69. Java Stored Procedure coderanch.com |
70. Getting value from stored procedure ?? coderanch.comHi, I am calling a store procedure from Java, I am not sure how i'll recieve the value of the parameter with "OUT" tag in store procedure. Will, i receive this variable in Java as a result set, or it'll be stored in the variable which is used to when i was calling the store procedure. sorry for English. |
71. Java stored procedures and collections(ORCL) coderanch.com |
72. Stored Procedure Question coderanch.comI have a bill of material maintenance program that I have implemented an error check into. Currently how it works, is it reads each part number that is listed on the bill and runs a stored procedure to get information about that part number to make sure there are no errors on it. The problem... some bills of material have 300+ ... |
73. VB vs Java stored procedure performance coderanch.comStored procedures live in the database, so nothing should have changed with them. You probably need to do some investigation to see how long they take independent of your application and see if there are any problems. A stored procedure that takes five minutes to execute is very slow. I don't know what tools Sybase provide, but you should be able ... |
74. stored procedures coderanch.comHello all, I am new to JDBC and am currently following The Java Tutorial for this. I am working on a program where I am supposed to update a database using stored procedures. The database has a primary key column and I am first to query the database to know the maximum value of this key and then add 1 to ... |
75. stored procedure mismatch coderanch.comHi .. I have a Stored Proc which has input params as p_papa_user_party_id_in IN --> Integer p_pasu_user_version_nr_in IN --> Number p_user_login_tx_in IN --> VARCHAR p_cdt_display_language_cd_in IN --> VARCHAR p_cdt_time_zone_cd_in IN --> VARCHAR p_cdt_date_format_cd_in IN --> VARCHAR p_cdt_number_format_cd_in IN --> VARCHAR p_cdt_currency_cd_in IN --> VARCHAR p_local_timestamp_in_in IN --> VARCHAR p_adj_daylight_savings_in_in IN --> VARCHAR p_comment_tx_in IN --> VARCHAR p_system_user_attributes_xt_in IN --> XMLTYPE p_latest_in_in ... |
76. Stored Procedures ,,,pros and cons coderanch.comGood if you know without a doubt: 1) you will never switch databases 2) you will never need to rewrite the procedure. The problem with them is if you write too many (hundreds) trying to update them later for small changes or new databases can be nearly impossible. In general, I try to avoid them at all costs unless they are ... |
77. stored procedure coderanch.com |
78. JDBC and Stored Procedure coderanch.comI'm not completely clear on your question, but are you referring to things that you can do in VB for example like simply specifying a table name instead of a complete SQL SELECT statement? If so, then no. JDBC requires that you actually execute SQL statements. Hibernate (http://www.hibernate.org) is a framework that allows you do object/relational mapping from Java. It has ... |
79. Only one question in Stored procedure. coderanch.comHi, What is the scenario we will use the stored procedure? I understand that will improve the performance while use the stored procedure and also reduce the application overload. My Question is like: 1) Even though we use the database connection in our application for using the stored procedure right? 2) Why we are not using like simply connecting to database ... |
80. WITHOUT STORED PROCEDUREs coderanch.comHi, I am using a stored Procedure in SQL server. Area is a Table. It has two coloumns Area_Name and Area_Code(Primary Key, AutoGenerated keys). I am inserting "name of the Area" as Area_Name. If the Similar Area name is pre existing in the table Area the value will never get inserted and the Store Procedure will return 1, else the Area ... |
81. Can I write Java on database-server instead of stored procedures? coderanch.com |
82. to use stored procedure or not coderanch.comHi I am one of those person who used to and still use the stored procedures. Not because I like it, in fact I have to depend on SQL developers to develop or optimize the stored procedure. Now, as I read more and more articles on good design, I am beginning to rethink on my practice of using stored procedures(SP). Actually ... |
83. Create Stored Procedure coderanch.com |
84. Stored Procedures learning coderanch.comHi D Bang, If you can look into the documentation given by the database vendor, you would definitely get it. That itself would suffice on how to do with the features supported by the product. But if you really wanted to read some theory/story behind stored procedures and what are they, why are they, what benefits they give, just google for ... |
85. Stored procedure question coderanch.com |
86. Stored Procedures coderanch.comI hate saying no, but I have to. ;) We focused on the 80% of SQL that you really need to know, and went into great depth. We figure once you know all the most commonly used stuff really really well, you won't have any problem picking up a great (O'Reilly) reference to get the rest. |
87. HF-SQL and Stored Procedures coderanch.com |
88. Passing multiple values to a stored procedure coderanch.comFrom AskTom (Tom Kyte the Oracle VP) http://download-east.oracle.com/docs/cd/A81042_01/DOC/java.816/a81354/oraarr3.htm#1056648 Here is a quick and dirty example showing PLSQL calling Java calling PLSQL and passing an array from Java to PLSQL. The trick is to use a SQL table type -- not a PLSQL type (eg: create the type OUTSIDE of plsql -- that way Java can in fact bind to it). Java ... |
89. Stored Procedure Issue coderanch.comSo I have what could be considered a very long question, but I'll try to trim it down to a reasonable size. Basically, I was originally using a stored procedure (on MS SQL Server) which utilized an OUTPUT parameter - something like this: DECLARE @startDate datetime, DECLARE @endDate datetime, DECLARE @numberOfMatchingRecords int OUTPUT SELECT @numberOfMatchingRecords = COUNT(*) FROM MyTable WHERE eventDt ... |
90. JDBC, Create Stored Procedure, delimiters coderanch.comHello everyone, I've been writing an application that will be running on top of MySQL 5.0+. I have deployment all taken care of, save for one (hopefully minor) detail. On my first attempt, JDBC threw up at the sight of semicolons in my statements (MySQLSyntaxErrorException.) I've gotten it to work by creating statements out of the main file -> to String ... |
91. stored procedures execution locks coderanch.comhi gurus, we have an application that calls some stored procedures (oracle9i). those procedures are using different tables and are not related. everything worked fine for a long time but today we've noticed a strange behavior: when one stored procedure is triggered, other procedures executed while the first one is still running are blocked and wait, until that first procedure is ... |
92. stored procedure problem!!! coderanch.com |
93. JDBC and stored procedure coderanch.com |
94. JDBC ans Stored Procedures coderanch.com |
95. Stored Procedure Problem coderanch.com |
96. Stored procedures and arrays/lists coderanch.comHi, This isn't strictly speaking a Java issue, but I'm writing an application using stored procedures to access a SQL Server database, and I have a situation where I need to add multiple rows to a table (it's coming from a multi-select list box). I'm wondering is there any other way of doing it other that repeatedly calling the same stored ... |
97. Need for stored procedure.. coderanch.com |
98. Stored procedure cannot be imported from java...? coderanch.comHi Members, Im having my 'create procedures' syntax in a file and im reading it line-by-line and storing it in a string and finally executing that string..... however this is not accepting the 'Drop function if exists' syntax within the file.... it throws an sql exception error. example: (which throws error) *********************** DELIMITER | DROP FUNCTION IF EXISTS GETREGRESSIONRESULTID| CREATE FUNCTION ... |
99. Prepared statemnt vs Stored procedure coderanch.comHi, I have some confusion regarding stored procedure and prepared statements. I know SP are useful in cases where you have to make changes to a number of tables. In such cases you can group the logic in a SP which results in the business logic being present in the DB. I usually like the BL to be present in my ... |
100. Timeout in Stored Procedure coderanch.comIn Message broker, from Java compute node, I am calling a oracle stored procedure using callable statement. In that I am setting the timeout using setQueryTimeout(). But setQueryTimeout() has some issues when the database server is hung or when there are network issues. I am calling the stored procedure using conDb.prepareCall(). I want to know what are the other ways of ... |